﻿using System;
using System.Collections;
using System.IO;
using System.Net;
using System.Text;
using GMap.NET;
using System.Collections.Generic;
using System.Data;

namespace InteractiveMapApp
{
    /// <summary>
    /// Helper Class to deal with the story/marker objects since there is so much filtering going on now
    /// filter by category, timeline and location (based on Map.CurrentViewArea) - location being the most intensive!
    /// This class is created to keep code separate and easy to understand all in one area(and hopefully as a result, will get 
    /// chance to optimize everything), specially to deal with the downloaded storiesArrayList (of which we intend to fill 
    /// up to size of possibly thousands).
    /// </summary>
    public class StoriesHelperClass
    {
        #region Cat/Timeslot/Stories Collections
        /// <summary>
        /// Available Categories
        /// * Store objects of Category class
        /// * This is initialized upon application startup period and 
        /// never modified again. If there is a change to Categories in the DB, it will take effect
        /// after application restart.
        /// </summary>
        private ArrayList categoriesArrayList = new ArrayList();

        /// <summary>
        /// Available TimeSlots
        /// * Store objects of TimeSlot class
        /// * This is initialized upon application startup period and 
        /// never modified again. If there is a change to TimeSlot in the DB, it will take effect
        /// after application restart.
        /// </summary>
        private ArrayList timeSlotArrayList = new ArrayList();

        /// <summary>
        /// Downloaded 'user' stories
        /// * Store objects of Story class
        /// * Initially, the stories get here by all being downloaded from server
        /// * After that, this list is used to hold only a small number of stories before
        /// they are sent over to the View to display them on screen.
        /// </summary>
        private ArrayList storiesArrayList = new ArrayList();
        public ArrayList StoriesArrayList
        {
            get { return storiesArrayList; }
            set { storiesArrayList = value; }
        }

        /// <summary>
        /// Downloaded 'Museum' stories
        /// * Store objects of Story class
        /// * Initially, the stories get here by all being downloaded from server
        /// * After that, this list is used to hold only a small number of stories before
        /// they are sent over to the View to display them on screen.
        /// </summary>
        private ArrayList museumStoriesArrayList = new ArrayList();
        #endregion

        #region Info. for dealing with server Requests
        static string RequestPageURL = Properties.Settings.Default.RequestPageURL;
        const string LOADSTORYREQUEST = "rn=loadStories";
        const string LOADCATEGORYREQUEST = "rn=loadCategories";
        const string LOADTIMESLOTSREQUEST = "rn=loadTimeSlots";
        const string LOADRECENTSTORYREQUEST = "rn=loadRecentStories";
        const string LOADMUSEUMSTORYREQUEST = "rn=loadMuseumStories";
        #endregion

        const string ERRORSTRING = "ERROR";
        const char STRINGSPLITCHAR = '\'';

        SQLiteDBHelper database;

        /// <summary>
        /// Constructor for StoriesHelper Class
        /// 
        /// Remember to call SetUpDB()!
        /// </summary>
        public StoriesHelperClass()
        {

        }

        #region Initialisation Code : setUpDB() etc.
        /// <summary>
        /// Pretty much makes sure the application is good to go.
        /// Starts out by checking if the localDB is old or not
        /// If its old, it will refresh it from the remoteDB
        /// Once it knows its not old, it will load up some local collections
        /// from the DB and return true once its all set up.
        /// </summary>
        /// <returns>true if everything was all good!</returns>
        public bool SetUpDB(bool projectorMode)
        {
            bool returnVal = true;
            try
            {
                database = new SQLiteDBHelper(":memory:;Version=3;New=True;Pooling=True;Max Pool Size=1;");
                //create the tables!
                Console.WriteLine("Creating in-memory DB");
                string create_cat = @"CREATE TABLE [CATEGORY] (
                [img_UNselectedInputbox] TEXT  NULL,
                [img_selectedInputBox] TEXT  NULL,
                [unselectedFilterImage] TEXT  NULL,
                [adminStory] NUMERIC  NULL,
                [markerNormalImgUrl] TEXT  NULL,
                [normalImgURL] TEXT  NULL,
                [categoryDesc] TEXT  NULL,
                [categoryID] INTEGER  NOT NULL PRIMARY KEY,
                [isItFilteredOut] TEXT  NULL
                );";
                database.CreateTheTables(create_cat);
                Console.WriteLine("CATEGORY Table initialized");

                string create_time= @"CREATE TABLE [TIMESLOT] (
                [unselectImgURL] TEXT  NULL,
                [normalImgURL] TEXT  NULL,
                [timeslotID] INTEGER  NOT NULL PRIMARY KEY,
                [isItFilteredOut] TEXT  NULL,
                [read_URL] TEXT NULL
                );";
                database.CreateTheTables(create_time);
                Console.WriteLine("TIMESLOT Table initialized");

                string create_story= @"CREATE TABLE [STORY] (
                [storyID] INTEGER  NULL PRIMARY KEY,
                [categoryID] NUMERIC  NULL,
                [timeslotID] NUMERIC  NULL,
                [latitude] NUMERIC  NULL,
                [longitude] NUMERIC  NULL,
                [content] TEXT  NULL,
                [museumImgURL] TEXT  NULL,
                [title] TEXT  NULL
                );";
                database.CreateTheTables(create_story);
                Console.WriteLine("STORY Table initialized");

                //download all the info from server and store locally (temp)
                if (setUpDB_categories())
                {
                    if (setUpDB_timeslots())
                    {
                        //stories in nested IF because story has key dependencies on cat/timeslots
                        setUpDB_stories(projectorMode);
                    }
                    else
                    {
                        Console.WriteLine("Error : Failure to load TimeSlots, application startup terminated.");
                        returnVal = false;
                    }
                }
                else
                {
                    Console.WriteLine("Error : Failure to load Categories, application startup terminated.");
                    returnVal = false;
                }
                //Populate collections with data from localDB
                LoadLocalDataFromDB(projectorMode);
                #region previously used, with flatfile DB
                //database = new SQLiteDBHelper("localDB.db");
                ////should we re-download from DB or use the stored file?
                //DateTime fileLastWrite = File.GetLastWriteTime("localDB.db");
                
                //if (DateTime.Now > fileLastWrite.AddSeconds(5))
                ////if (DateTime.Now > fileLastWrite.AddHours(1))
                ////if (DateTime.Now > fileLastWrite.AddHours(6))
                //{
                //    //its too old, flush the existing records
                //    Console.WriteLine("Alert : Stored data was outdated. Updating records.");
                //    database.ClearTable("STORY");
                //    database.ClearTable("TIMESLOT");
                //    database.ClearTable("CATEGORY");
                //    //download all the info from server and store locally (temp)
                //    if (setUpDB_categories())
                //    {
                //        if (setUpDB_timeslots())
                //        {
                //            //stories in nested IF because story has key dependencies on cat/timeslots
                //            setUpDB_stories();
                //        }
                //        else
                //        {
                //            Console.WriteLine("Error : Failure to load TimeSlots, application startup terminated.");
                //            returnVal = false;
                //        }
                //    }
                //    else
                //    {
                //        Console.WriteLine("Error : Failure to load Categories, application startup terminated.");
                //        returnVal = false;
                //    }
                //}
                ////Populate collections with data from localDB
                //LoadLocalDataFromDB();
                #endregion
            }
            catch
            {
                Console.WriteLine("Error : Local DB Init. Failure, application startup terminated.");
                returnVal = false;
            }
            return returnVal;
        }

        /// <summary>
        /// Populate the class collections with data from local DB
        /// </summary>
        private void LoadLocalDataFromDB(bool projectorMode)
        {
            //set everything up in order of Parents -> Children (db wise)
            LoadCategoryFromLocalDBToArrayList();
            LoadTimeSlotFromLocalDBToArrayList();
            if (!projectorMode)
            {
                UpdateAllCategoryFilterStatus(categoriesArrayList, true);
                UpdateAllTimeSlotFilterStatus(timeSlotArrayList, true);
            }
            GetRandomFilteredUserStories(true);
            GetRandomFilteredAdminStories(true);
        }

        /// <summary>
        /// Load Timeslots arraylist with
        /// timeslot objects stored in the local db
        /// </summary>
        private void LoadTimeSlotFromLocalDBToArrayList()
        {
            DateTime startTime = DateTime.Now;
            Console.WriteLine("Progress : Setting up TimeSlots (may take some time, d/l 3x images for each TimeSlot)");
            string query = "SELECT timeslotID, normalImgURL, unselectImgURL, read_URL FROM TIMESLOT ORDER BY timeslotID ASC";
            DataTable timeslotsDT = database.GetDataTable(query);
            foreach (DataRow dr in timeslotsDT.Rows)
            {
                timeSlotArrayList.Add(new TimeSlot(Convert.ToInt32(dr[0]), dr[1].ToString(), dr[2].ToString(), dr[3].ToString()));
            }
            PrintTimeTaken(startTime, "Setup TimeSlots");
        }

        /// <summary>
        /// Load categories arraylist with 
        /// category objects stored in the local DB
        /// </summary>
        private void LoadCategoryFromLocalDBToArrayList()
        {
            Console.WriteLine("Progress : Setting up Categories (may take some time, d/l 5x images for each Category)");
            DateTime startTime = DateTime.Now;
            string query = "SELECT categoryID, adminStory, img_selectedInputBox, img_UNselectedInputbox, ";
            query += "normalImgURL, unselectedFilterImage, markerNormalImgUrl, categoryDesc FROM CATEGORY ";
            query += "ORDER BY categoryID ASC";
            DataTable categoriesDT = database.GetDataTable(query);
            foreach (DataRow dr in categoriesDT.Rows)
            {
                bool isAdminCat = false;
                if (dr[1].ToString().Equals("1")) { isAdminCat = true; }
                categoriesArrayList.Add(new Category(Convert.ToInt32(dr[0]), dr[7].ToString(), dr[4].ToString(), dr[6].ToString(),
                    isAdminCat, dr[5].ToString(), dr[2].ToString(), dr[3].ToString()));
            }
            PrintTimeTaken(startTime, "Setup Categories");
        }

        /// <summary>
        /// Add to the storiesArrayList from a DataTable
        /// The values in DT should be as follows :
        /// STORY TABLE
        /// storyID, categoryID, timeslotID, latitude, longitude, content
        /// </summary>
        /// <param name="storyDT">datatable of stories</param>
        /// <param name="adminStoryList">true: add to museumStoriesArrayList, false: add to storiesArrayList</param>
        private void addStoriesFromDataTable(DataTable storyDT, bool adminStoryList)
        {
            if (adminStoryList)
            {
                foreach (DataRow dr in storyDT.Rows)
                {
                    museumStoriesArrayList.Add(new Story(Convert.ToInt64(dr[0]), Convert.ToInt32(dr[1]), Convert.ToInt32(dr[2]),
                            Convert.ToDouble(dr[3]), Convert.ToDouble(dr[4]), dr[5].ToString(), dr[6].ToString(), dr[7].ToString()));
                }
            }
            else
            {
                foreach (DataRow dr in storyDT.Rows)
                {
                    storiesArrayList.Add(new Story(Convert.ToInt64(dr[0]), Convert.ToInt32(dr[1]), Convert.ToInt32(dr[2]),
                        Convert.ToDouble(dr[3]), Convert.ToDouble(dr[4]), dr[5].ToString(), null, dr[6].ToString()));
                }
            }
        }

        /// <summary>
        /// Download stories (temporarily) to arraylist
        /// then permanently store them to the STORY table
        /// in the database and Console.WriteLine the time taken
        /// * Loads both user AND admin stories into the DB
        /// Using 2 separate requests for them still but for good reason:
        /// the TOP few returned stories might all be user stories so its good to have
        /// separate query to ask just for museum stories
        /// </summary>
        /// <returns>true if successful</returns>
        private bool setUpDB_stories(bool projectorMode)
        {
            DateTime startingTime = DateTime.Now;
            bool returnVal = true;
            int totalAdminStories;
            ArrayList tempStories;

            if (!projectorMode)
            {
                Console.WriteLine("Downloading User Stories from DB (latest 1000)");
                tempStories = DownloadStoriesFromDB(false);
                int totalUserStories = tempStories.Count;
                PrintTimeTaken(startingTime, totalUserStories.ToString() + " User stories downloaded");
                Console.WriteLine("Downloading Admin Stories from DB (latest 100)");
                startingTime = DateTime.Now;
                tempStories.AddRange(DownloadStoriesFromDB(true));
                totalAdminStories = tempStories.Count - totalUserStories;
            }
            else //projector mode
            {
                Console.WriteLine("Downloading User Stories from DB (latest 1000)");
                tempStories = DownloadStoriesFromDB(false);
                int totalUserStories = tempStories.Count;
                PrintTimeTaken(startingTime, totalUserStories.ToString() + " User stories downloaded");
                Console.WriteLine("Downloading Admin Stories from DB (latest 100)");
                startingTime = DateTime.Now;
                tempStories.AddRange(DownloadStoriesFromDB(true));
                totalAdminStories = tempStories.Count - totalUserStories;
            }

            PrintTimeTaken(startingTime, totalAdminStories.ToString() + " Admin stories downloaded");
            int count = tempStories.Count;
            if (tempStories == null || count <= 0)
            {
                returnVal = false;
            }
            else
            {
                startingTime = DateTime.Now;
                database.MassInsertStories(tempStories);
                PrintTimeTaken(startingTime, "Added " + count.ToString() + " Stories to DB. ");
                #region another way(old way) to mass insert stories (but its slower!)
                /*
                    //THIS ONE TAKES ~ 1.5 seconds for 30 story records
                    //foreach (Story i in temp)
                    //{
                    //    Dictionary<String, String> data = new Dictionary<String, String>();
                    //    data.Add("storyID", i.StoryID.ToString());
                    //    data.Add("categoryID", i.CategoryID.ToString());
                    //    data.Add("timeslotID", i.TimeSlotID.ToString());
                    //    data.Add("latitude", i.StoryLatLng.Lat.ToString());
                    //    data.Add("longitude", i.StoryLatLng.Lng.ToString());
                    //    data.Add("content", i.StoryContent);

                    //    database.Insert("STORY", data);
                    //}
                    */
                #endregion
            }

            return returnVal;
        }

        /// <summary>
        /// Download categories (temporarily) to arraylist
        /// then permanently store them to the CATEGORY table
        /// in the database and Console.WriteLine the time taken
        /// </summary>
        /// <returns>true if successful</returns>
        private bool setUpDB_categories()
        {
            DateTime startingTime = DateTime.Now;
            bool returnVal = true;
            Console.WriteLine("Downloading Categories from DB");
            ArrayList tempCategories = DownloadCategoriesFromDB();
            int count = tempCategories.Count;
            PrintTimeTaken(startingTime, "Downloaded "+ count.ToString() +" Cats.");
            if (tempCategories == null || count <= 0)
            {
                returnVal = false;
            }
            else
            {
                startingTime = DateTime.Now;
                database.MassInsertCategories(tempCategories);
                PrintTimeTaken(startingTime, "Added " + count.ToString() + " Categories to DB. ");
            }
            return returnVal;
        }

        /// <summary>
        /// Download timeslots (temporarily) to arraylist
        /// then permanently store them to the TIMESLOT table
        /// in the database and Console.WriteLine the time taken
        /// </summary>
        /// <returns>true if successful</returns>
        private bool setUpDB_timeslots()
        {
            DateTime startingTime = DateTime.Now;
            bool returnVal = true;
            Console.WriteLine("Downloading Timeslots from DB");
            ArrayList tempTimeslots = DownloadTimeSlotsFromDB();
            int count = tempTimeslots.Count;
            PrintTimeTaken(startingTime, "Downloaded " + count.ToString() + " Timeslots");
            if (tempTimeslots == null || count <= 0)
            {
                returnVal = false;
            }
            else
            {
                startingTime = DateTime.Now;
                database.MassInsertTimeSlots(tempTimeslots);
                PrintTimeTaken(startingTime, "Added " + count.ToString() + " TimeSlots to DB. ");
            }
            return returnVal;
        }

        /// <summary>
        /// Download TimeSlots from the remote DB
        /// </summary>
        /// <returns>araylist of timeslot objects</returns>
        private ArrayList DownloadTimeSlotsFromDB()
        {
            ArrayList timeslots = new ArrayList();

            string downloadedTimeSlots = downloadRequestFromDB(LOADTIMESLOTSREQUEST);
            if (downloadedTimeSlots.Equals(ERRORSTRING))
            {
                Console.WriteLine("Error : Received error message when trying to Request(TimeSlots)  from server");
            }
            else
            {
                if (downloadedTimeSlots.Equals(""))
                {
                    Console.WriteLine("Error : There are no TimeSlots received from the DB");
                }
                else
                {
                    string[] timeSlotsArray = downloadedTimeSlots.Split(STRINGSPLITCHAR);
                    int i = 0;
                    while (i < timeSlotsArray.Length - 1)
                    {
                        try
                        {
                            timeslots.Add(new TimeSlot(Convert.ToInt32(timeSlotsArray[i]), timeSlotsArray[i + 1].ToString(),
                                timeSlotsArray[i + 2].ToString(), timeSlotsArray[i+3].ToString()));
                            i += 4;
                        }
                        catch { Console.WriteLine("Error : Exception adding TimeSlot at i = " + i.ToString()); i += 4; }
                    }

                }
            }
            return timeslots;
        }

        /// <summary>
        /// Download Categories from the remote DB
        /// </summary>
        /// <returns>arraylist of Category objects</returns>
        private ArrayList DownloadCategoriesFromDB()
        {
            ArrayList categories = new ArrayList();

            string downloadedCategories = downloadRequestFromDB(LOADCATEGORYREQUEST);
            if (downloadedCategories.Equals(ERRORSTRING))
            {
                Console.WriteLine("Error : Received error message when trying to Request(Categories)  from server");
            }
            else
            {
                if (downloadedCategories.Equals(""))
                {
                    Console.WriteLine("Error : There are no Categories received from the Server DB");
                }
                else
                {
                    string[] categoriesArray = downloadedCategories.Split(STRINGSPLITCHAR);
                    int i = 0;
                    bool adminCtg;
                    while (i < categoriesArray.Length - 1)
                    {
                        try
                        {
                            adminCtg = false;
                            if (categoriesArray[i + 4].ToString().Equals("1") || categoriesArray[i + 4].ToLower().Equals("true")) { adminCtg = true; }
                            categories.Add(new Category(Convert.ToInt32(categoriesArray[i]), categoriesArray[i + 1].ToString(), categoriesArray[i + 2].ToString(), categoriesArray[i + 3].ToString(), adminCtg, categoriesArray[i + 5].ToString(),
                                categoriesArray[i + 6].ToString(), categoriesArray[i + 7].ToString()));
                            i += 8;
                        }
                        catch { Console.WriteLine("Error : Exception adding Category at i = " + i.ToString()); i += 8; }
                    }
                }
            }
            return categories;
        }

        /// <summary>
        /// Download Stories from DB
        /// </summary>
        /// <param name="adminStories">true: download admin stories,false: download user stories</param>
        /// <returns>arraylist of Story objects</returns>
        private ArrayList DownloadStoriesFromDB(bool adminStories)
        {
            ArrayList stories = new ArrayList();
            string downloadedStories = "";
            if (adminStories)
            {
                downloadedStories = downloadRequestFromDB(LOADMUSEUMSTORYREQUEST);
            }
            else
            {
                downloadedStories = downloadRequestFromDB(LOADSTORYREQUEST);
            }

            if (downloadedStories.Equals(ERRORSTRING))
            {
                if (adminStories)
                {
                    Console.WriteLine("Error : Received error message when trying to Request(AdminStories) from server");
                }
                else
                {
                    Console.WriteLine("Error : Received error message when trying to Request(UserStories) from server");
                }
            }
            else
            {
                if (downloadedStories.Equals(""))
                {
                    if (adminStories)
                    {
                        Console.WriteLine("Error : There are no Admin Stories received from the DB");
                    }
                    else
                    {
                        Console.WriteLine("Error : There are no User Stories received from the DB");
                    }
                }
                else
                {

                    string[] storiesArray = downloadedStories.Split(STRINGSPLITCHAR);
                    int i = 0;
                    while (i < storiesArray.Length - 1)
                    {
                        try
                        {
                            if (adminStories)
                            {
                                stories.Add(new Story(Convert.ToInt64(storiesArray[i]), Convert.ToInt32(storiesArray[i + 1]),
                                    Convert.ToInt32(storiesArray[i + 2]), Convert.ToDouble(storiesArray[i + 3]),
                                    Convert.ToDouble(storiesArray[i + 4]), storiesArray[i + 5], storiesArray[i + 6], storiesArray[i + 7]));
                                i += 8;
                            }
                            else
                            {
                                stories.Add(new Story(Convert.ToInt64(storiesArray[i]), Convert.ToInt32(storiesArray[i + 1]),
                                    Convert.ToInt32(storiesArray[i + 2]), Convert.ToDouble(storiesArray[i + 3]),
                                    Convert.ToDouble(storiesArray[i + 4]), storiesArray[i + 5], null, storiesArray[i + 6]));
                                i += 7;
                            }
                        }
                        catch { Console.WriteLine("Error : Exception adding Story at i = " + i.ToString()); if(adminStories){ i+= 8;} else{ i+=7;} }
                    }

                }
            }
            return stories;
        }

        /// <summary>
        /// Add a story to the remote DB
        /// </summary>
        /// <param name="catID">stories categoryID</param>
        /// <param name="story">story content</param>
        /// <param name="storyPosition">PointLatLng stories markers position</param>
        /// <param name="timeSlotID">timeslotID</param>
        /// <returns></returns>
        public string AddStoryToRemoteDB(int catID, string story, PointLatLng storyPosition, int timeSlotID, string machineName, string title, string errorPostback)
        {
            try
            {
                WebRequest request = WebRequest.Create(RequestPageURL);
                request.Method = "POST";
                string postData = "rn=addStory&cattid=" + catID.ToString() + "&story=" + story + "&lat=" + storyPosition.Lat.ToString() + "&lng=" +
                    storyPosition.Lng.ToString() + "&MachineName=" + machineName + "&TimeSlotID=" + timeSlotID.ToString() + "&Title=" + title;
                byte[] byteArray = Encoding.UTF8.GetBytes(postData);
                request.ContentType = "application/x-www-form-urlencoded";
                request.ContentLength = byteArray.Length;
                Stream dataStream = request.GetRequestStream();
                dataStream.Write(byteArray, 0, byteArray.Length);
                dataStream.Close();
                WebResponse response = request.GetResponse();
                dataStream = response.GetResponseStream();
                StreamReader reader = new StreamReader(dataStream);
                string responseFromServer = reader.ReadToEnd();
                reader.Close();
                dataStream.Close();
                response.Close();
                return responseFromServer;
            }
            catch
            {
                return errorPostback;
            }
        }

        /// <summary>
        /// Uses the MassInsertStories method to 
        /// efficiently insert lots of records to the DB.
        /// </summary>
        /// <param name="stories">arraylist of Story objects</param>
        public void InsertUsersNewStoriesIntoDB(ArrayList stories)
        {
            DateTime startTime = DateTime.Now;
            database.MassInsertStories(stories);
            PrintTimeTaken(startTime, "Inserted " + stories.Count + " new stories to DB");
        }

        /// <summary>
        /// Removes stories from the local database
        /// </summary>
        /// <param name="stories">arraylist of Story objects</param>
        public void RemoveStoriesFromDB(long ID)
        {
            database.Delete("STORY", "`StoryID` = " + ID);
        }

        /// <summary>
        /// Mass inserts all the downloaded new 
        /// user stories from remoteDB to localDB
        /// </summary>
        public ArrayList DownloadRecentStoriesIntoDB()
        {
            ArrayList temp = new ArrayList();
            temp.AddRange(DownloadNEWUserStoriesFromDB());
            DateTime startTime = DateTime.Now;
            if (temp.Count > 0)
            {
                database.MassInsertStories(temp);
            }
            PrintTimeTaken(startTime, temp.Count.ToString() + " New Stories Fetched");
            return temp;
        }


        /// <summary>
        /// Downloads the recent stories from remoteDB (only user stories)
        /// </summary>
        /// <returns></returns>
        private ArrayList DownloadNEWUserStoriesFromDB()
        {
            ArrayList stories = new ArrayList();
            string downloadedStories = downloadRequestFromDB(LOADRECENTSTORYREQUEST);

            if (downloadedStories.Equals(ERRORSTRING))
            {
                Console.WriteLine("Error : Received error message when trying to Request(RecentStories) from server");
            }
            else
            {
                if (downloadedStories.Equals(""))
                {
                    Console.WriteLine("There are no new User Stories received from the DB");
                }
                else
                {
                    string[] storiesArray = downloadedStories.Split(STRINGSPLITCHAR);
                    int i = 0;
                    while (i < storiesArray.Length - 1)
                    {
                        try
                        {
                                stories.Add(new Story(Convert.ToInt64(storiesArray[i]), Convert.ToInt32(storiesArray[i + 1]),
                                    Convert.ToInt32(storiesArray[i + 2]), Convert.ToDouble(storiesArray[i + 3]),
                                    Convert.ToDouble(storiesArray[i + 4]), storiesArray[i + 5], null, storiesArray[i + 6]));
                                i += 7;
                        }
                        catch { Console.WriteLine("Error : Exception adding New Story at i = " + i.ToString()); i += 7;  }
                    }

                }
            }
            return stories;
        }
        #endregion

    
        #region Accessor methods (get___)

        /// <summary>
        /// Get arraylist containing all category objects
        /// </summary>
        /// <returns>arraylist containing all category objects</returns>
        public ArrayList GetCategoriesArrayList()
        {
            return categoriesArrayList;
        }

        /// <summary>
        /// Get arraylist containing all timeslot objects
        /// </summary>
        /// <returns>arraylist containing all timeslot objects</returns>
        public ArrayList GetTimeSlotsArrayList()
        {
            return timeSlotArrayList;
        }

        /// <summary>
        /// You will receive whatever set of admin stories are inside
        /// currently filtered arraylist, this method will not go back to DB
        /// to randomize or re-fetch filtered stories, just return what 
        /// has already been fetched
        /// </summary>
        /// <returns>arraylist of user stories</returns>
        public ArrayList GetAdminStoriesArrayList()
        {
            return museumStoriesArrayList;
        }

        /// <summary>
        /// You will receive whatever set of USER stories are inside
        /// currently filtered arraylist, this method will not go back to DB
        /// to randomize or re-fetch filtered stories, just return what 
        /// has already been fetched
        /// </summary>
        /// <returns>arraylist of user stories</returns>
        public ArrayList GetUserStoriesArrayList()
        {
            return storiesArrayList;
        }

        /// <summary>
        /// Get a arraylist of totally admin stories from DB
        /// the size of arraylist is maxed out at what the settings file sets the
        /// MaxNumberMuseumStoriesToShowAtATime
        /// These stories do not filter by timeline (admin story show all the time!)
        /// </summary>
        /// <returns>arraylist of random admin stories</returns>
        int admin_story_limit = Properties.Settings.Default.MaxNumberMuseumStoriesToShowAtATime;
        public ArrayList GetRandomFilteredAdminStories(bool printTime)
        {
            DateTime startTime = DateTime.Now;
            //if (printTime) { Console.WriteLine("Progress : Setting up Admin Stories (shouldn't be long)"); }
            string query = "SELECT storyID, categoryID, timeslotID, latitude, longitude, content, museumImgURL, title ";
            query += "FROM STORY WHERE categoryID IN ";
            query += "(SELECT categoryID from CATEGORY WHERE adminStory = '1') ";
            query += "AND timeslotID in(select timeslotID FROM TIMESLOT) ";
            query += "ORDER BY RANDOM() ";
            query += "LIMIT " + admin_story_limit.ToString();
            //empty the current collection of admin stories first
            museumStoriesArrayList = new ArrayList();
            addStoriesFromDataTable(database.GetDataTable(query), true);
            if (printTime) { PrintTimeTaken(startTime, "Setup Admin Stories"); }
            return museumStoriesArrayList;
        }

        /// <summary>
        /// Get arraylist of totally random admin stories from DB
        /// This override version of above method takes RectLatLng
        /// as a parameter and uses that to return list of stories
        /// which are only within that rect area.
        /// </summary>
        /// <param name="printTime">if true, print processing time</param>
        /// <param name="currentViewArea">used to determine which area you want stories in</param>
        /// <returns>arraylist of stories</returns>
        public ArrayList GetRandomFilteredAdminStories(bool printTime, RectLatLng currentViewArea, bool areAllCatFiltersTheSame, bool areAllTimeFiltersTheSame)
        {
            DateTime startTime = DateTime.Now;
            //if (printTime) { Console.WriteLine("Progress : Setting up Admin Stories (shouldn't be long)"); }
            string query = "SELECT * FROM (";
            query += "SELECT storyID, categoryID, timeslotID, latitude, longitude, content, museumImgURL, title ";
            query += "FROM STORY WHERE categoryID IN ";
            if (areAllCatFiltersTheSame)
            {
                query += "(SELECT categoryID from CATEGORY WHERE adminStory = '1') ";
            }
            else
            {
                query += "(SELECT categoryID from CATEGORY WHERE adminStory = '1' AND isItFilteredOut = 'true') ";
            }
            if (areAllTimeFiltersTheSame)
            {
                query += "AND timeslotID in(select timeslotID FROM TIMESLOT) ";
            }
            else
            {
                query += "AND timeslotID in(select timeslotID FROM TIMESLOT WHERE isItFilteredOut = 'true') ";
            }
            query += "AND latitude <=" + currentViewArea.LocationTopLeft.Lat.ToString();
            query += " AND latitude >=" + currentViewArea.LocationRightBottom.Lat.ToString();
            query += " AND longitude >=" + currentViewArea.LocationTopLeft.Lng.ToString();
            query += " AND longitude <=" + currentViewArea.LocationRightBottom.Lng.ToString();
            query += " ORDER BY RANDOM() ";
            query += "LIMIT " + admin_story_limit.ToString();
            query += ") as random_results ORDER BY latitude DESC";
            //empty the current collection of admin stories first
            museumStoriesArrayList = new ArrayList();
            addStoriesFromDataTable(database.GetDataTable(query), true);
            if (printTime) { PrintTimeTaken(startTime, "Setup Admin Stories"); }
            return museumStoriesArrayList;
        }
        
        /// <summary>
        /// Get a arraylist of totally random user stories from DB
        /// the size of arraylist is maxed out at what the settings file sets the
        /// MaxNumberRandomStoriesToShowAtATime
        /// The stories inside are also all as per current cat/timeline filter rules
        /// </summary>
        /// <returns>arraylist of random user stories according to filters</returns>
        int user_story_limit = Properties.Settings.Default.MaxNumberRandomStoriesToShowAtATime;
        public ArrayList GetRandomFilteredUserStories(bool printTime)
        {
            DateTime startTime = DateTime.Now;
            //if (printTime) { Console.WriteLine("Progress : Setting up User Stories (shouldn't be long)"); }
            string query = "SELECT storyID, categoryID, timeslotID, latitude, longitude, content, title ";
            query += "FROM STORY WHERE categoryID IN ";
            query += "(SELECT categoryID from CATEGORY WHERE adminStory != '1') ";
            query += "AND timeslotID in(select timeslotID FROM TIMESLOT) ";
            query += "ORDER BY RANDOM() ";
            query += "LIMIT " + user_story_limit.ToString();
            //empty the current collection of user stories first
            storiesArrayList = new ArrayList();
            addStoriesFromDataTable(database.GetDataTable(query), false);
            if (printTime) { PrintTimeTaken(startTime, "Setup User Stories"); }
            return storiesArrayList;
        }

        /// <summary>
        /// Get arraylist of totally random user stories from DB
        /// This override version of above method takes RectLatLng
        /// as a parameter and uses that to return list of stories
        /// which are only within that rect area.
        /// </summary>
        /// <param name="printTime">if true, print processing time</param>
        /// <param name="currentViewArea">used to determine which area you want stories in</param>
        /// <returns>arraylist of stories</returns>
        public ArrayList GetRandomFilteredUserStories(bool printTime, RectLatLng currentViewArea, bool areAllCatFiltersTheSame, bool areAllTimeFiltersTheSame)
        {
            DateTime startTime = DateTime.Now;
            //if (printTime) { Console.WriteLine("Progress : Setting up User Stories (shouldn't be long)"); }
            string query = "SELECT * FROM (";
            query += "SELECT storyID, categoryID, timeslotID, latitude, longitude, content, title ";
            query += "FROM STORY WHERE categoryID IN ";
            if (areAllCatFiltersTheSame)
            {
                query += "(SELECT categoryID from CATEGORY WHERE adminStory != '1') ";
            }
            else
            {
                query += "(SELECT categoryID from CATEGORY WHERE adminStory != '1' AND isItFilteredOut = 'true') ";
            }
            if (areAllTimeFiltersTheSame)
            {
                query += "AND timeslotID in(select timeslotID FROM TIMESLOT) ";
            }
            else
            {
                query += "AND timeslotID in(select timeslotID FROM TIMESLOT WHERE isItFilteredOut = 'true') ";
            }
            query += "AND latitude <=" +currentViewArea.LocationTopLeft.Lat.ToString();
            query += " AND latitude >=" +currentViewArea.LocationRightBottom.Lat.ToString();
            query += " AND longitude >=" + currentViewArea.LocationTopLeft.Lng.ToString();
            query += " AND longitude <=" + currentViewArea.LocationRightBottom.Lng.ToString();
            query += " ORDER BY RANDOM() ";
            query += "LIMIT " + user_story_limit.ToString();
            query += ") as random_results ORDER BY latitude DESC";
            //empty the current collection of user stories first
            storiesArrayList = new ArrayList();
            addStoriesFromDataTable(database.GetDataTable(query), false);
            if (printTime) { PrintTimeTaken(startTime, "Setup User Stories"); }
            return storiesArrayList;
        }
        #endregion

        #region Change Filters Status's

        /// <summary>
        /// Set stories of a certain category to on or off
        /// Not recommended to use this for case where more than one filter
        /// will be updated (too many transactions, slow!)
        /// </summary>
        /// <param name="categoryID">id of the category</param>
        /// <param name="showFilter">true if you want to show it</param>
        public void FilterCategoryByID(int categoryID, bool showFilter)
        {
            Dictionary<String, String> data = new Dictionary<String, String>();
            if (showFilter)
            {
                data.Add("isItFilteredOut", "'false'");
            }
            else
            {
                data.Add("isItFilteredOut", "'true'");
            }
            database.Update("CATEGORY", data, "categoryID = " + categoryID.ToString());
        }
        /// <summary>
        /// Set stories of a timeslot to on or off
        /// Not recommended to use this for case where more than one filter
        /// will be updated (too many transactions, slow!)
        /// </summary>
        /// <param name="timeslotID">id of the timeslot</param>
        /// <param name="showFilter">true if you want to show it</param>
        public void FilterTimeSlotByID(int timeslotID, bool showFilter)
        {
            Dictionary<String, String> data = new Dictionary<String, String>();
            if (showFilter)
            {
                data.Add("isItFilteredOut", "'false'");
            }
            else
            {
                data.Add("isItFilteredOut", "'true'");
            }
            database.Update("TIMESLOT", data, "timeslotID = " + timeslotID.ToString());
        }

        public void UpdateAllCategoryFilterStatus(ArrayList categories, bool printTime)
        {
            DateTime startTime = DateTime.Now;
            database.MassUpdateCategoriesFilterStatus(categories);
            if (printTime) { PrintTimeTaken(startTime, "Reset Cat. Filters"); }
        }

        public void UpdateAllTimeSlotFilterStatus(ArrayList timeslots, bool printTime)
        {
            DateTime startTime = DateTime.Now;
            database.MassUpdateTimeSlotsFilterStatus(timeslots);
            if (printTime) { PrintTimeTaken(startTime, "Reset Time Filters"); }
        }
        
        #endregion

        #region various helper methods
        /// <summary>
        /// Made this method because printing time taken for task
        /// was going on a fair bit so far.. wanting to try locate which tasks
        /// take how long and from there see how much faster we can make them!
        /// </summary>
        /// <param name="startTime">send DateTime.Now of BEFORE the method to measure is</param>
        /// <param name="process">added before the start/finish/timetaken is displayed</param>
        private void PrintTimeTaken(DateTime startTime, string prefixText)
        {
            Console.WriteLine(prefixText + "- Time Taken:" + DateTime.Now.Subtract(startTime).TotalMilliseconds +"(ms)");
            //Console.WriteLine(prefixText + ": Started = " + startTime.TimeOfDay +
            //        " | Finished = " + DateTime.Now.TimeOfDay +
            //        " | Time Taken (ms) = " + DateTime.Now.Subtract(startTime).TotalMilliseconds);
        }


        /// <summary>
        /// sorts the arraylist by latitude so that they are drawn to the map appropriately
        /// </summary>
        /// <param name="listToSort">the arraylist that should be sorted</param>
        public void sortStoryArrayList(ArrayList listToSort)
        {
            //sort the tempStories so that the objects with higher latitudes are listed first in the arraylist
            //uses the selection sort algorithm
            int minNum;
            for (int j = 0; j < listToSort.Count - 1; j++)
            {
                if (listToSort[j] != null)
                {
                    //assign value to value of the incrementer
                    minNum = j;

                    for (int k = j + 1; k < listToSort.Count; k++)
                    {
                        if (listToSort[k] != null)
                        {
                            //check this index of the array to see if it's smaller
                            //than the index of the second loop
                            if ((listToSort[minNum] as Story).StoryLatLng.Lat < (listToSort[k] as Story).StoryLatLng.Lat)
                            {
                                minNum = k;
                            }
                        }
                    }

                    if (minNum != j)//sorts the objects
                    {
                        Story tempStory;

                        //temporary hold for attributes of object at the previous index
                        long stID = (listToSort[j] as Story).StoryID;
                        int catID = (listToSort[j] as Story).CategoryID;
                        int timeID = (listToSort[j] as Story).TimeSlotID;
                        PointLatLng latLng = (listToSort[j] as Story).StoryLatLng;
                        string content = (listToSort[j] as Story).StoryContent;
                        string museumImageURL = (listToSort[j] as Story).MuseumImgURL;
                        bool userStory = (listToSort[j] as Story).UserStory;
                        string title = (listToSort[j] as Story).Title;


                        //sets the new object to the proper place in the array
                        tempStory = new Story((listToSort[minNum] as Story).StoryID, (listToSort[minNum] as Story).CategoryID,
                            (listToSort[minNum] as Story).TimeSlotID, (listToSort[minNum] as Story).StoryLatLng.Lat,
                            (listToSort[minNum] as Story).StoryLatLng.Lng, (listToSort[minNum] as Story).StoryContent,
                            (listToSort[minNum] as Story).MuseumImgURL, (listToSort[minNum] as Story).Title);
                        tempStory.UserStory = (listToSort[minNum] as Story).UserStory;
                        listToSort[j] = tempStory;

                        //swaps the old objects position with the previous
                        tempStory = new Story(stID, catID, timeID, latLng.Lat, latLng.Lng, content, museumImageURL, title);
                        tempStory.UserStory = userStory;
                        listToSort[minNum] = tempStory;
                    }
                }
            }
        }


        /// <summary>
        /// This method fetches from the database the objects specified by the caller (timeslots, categories, stories)
        /// and adds all the data into a single string.
        /// The caller methods 'initialiseCategories()', 'initialiseTimeSlots()', or 'addDownloadedMarkersToMap()' split
        /// this string into pieces, making the data more accessible.
        /// The split string array is then processed by the application as necessary.
        /// </summary>
        /// <returns>a string which contains all the respective objects from the database</returns>
        private string downloadRequestFromDB(string downloadRequestName)
        {
            try
            {
                WebRequest request = WebRequest.Create(RequestPageURL);
                request.Method = "POST";
                string postData = downloadRequestName;
                byte[] byteArray = Encoding.UTF8.GetBytes(postData);
                request.ContentType = "application/x-www-form-urlencoded";
                request.ContentLength = byteArray.Length;
                Stream dataStream = request.GetRequestStream();
                dataStream.Write(byteArray, 0, byteArray.Length);
                dataStream.Close();
                WebResponse response = request.GetResponse();
                dataStream = response.GetResponseStream();
                StreamReader reader = new StreamReader(dataStream);
                string responseFromServer = reader.ReadToEnd();

                reader.Close();
                dataStream.Close();
                response.Close();

                //incase request name problem
                if (responseFromServer.Equals("undefined request!"))
                {
                    return ERRORSTRING;
                }
                else
                {
                    return responseFromServer;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: StoriesHelperClass.downloadRequestFromDB : \n" + e.Message);
                return ERRORSTRING;
            }
        }

        public static void reportStoryToDB(long storyID)
        {
            try
            {
                WebRequest request = WebRequest.Create(RequestPageURL);
                request.Method = "POST";
                string postData = "rn=ReportAStory&storyID=" + storyID;
                byte[] byteArray = Encoding.UTF8.GetBytes(postData);
                request.ContentType = "application/x-www-form-urlencoded";
                request.ContentLength = byteArray.Length;
                Stream dataStream = request.GetRequestStream();
                dataStream.Write(byteArray, 0, byteArray.Length);
                dataStream.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: StoriesHelperClass.reportStoryToDB : \n" + e.Message);
            }
        }

        public void CheckForObseleteStories()
        {
            try
            {
                WebRequest request = WebRequest.Create(RequestPageURL);
                request.Method = "POST";
                string postData = "rn=checkForRemovedStories";
                byte[] byteArray = Encoding.UTF8.GetBytes(postData);
                request.ContentType = "application/x-www-form-urlencoded";
                request.ContentLength = byteArray.Length;
                Stream dataStream = request.GetRequestStream();
                dataStream.Write(byteArray, 0, byteArray.Length);
                dataStream.Close();
                WebResponse response = request.GetResponse();
                dataStream = response.GetResponseStream();
                StreamReader reader = new StreamReader(dataStream);
                string responseFromServer = reader.ReadToEnd();

                reader.Close();
                dataStream.Close();
                response.Close();

                string[] tempStory = responseFromServer.Split(STRINGSPLITCHAR);
                foreach (string obseleteStoryID in tempStory)
                {
                    if (!obseleteStoryID.Equals(""))
                    {
                        RemoveStoriesFromDB(Convert.ToInt64(obseleteStoryID));
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: StoriesHelperClass.CheckForObseleteStories : \n" + e.Message);
            }
        }

        #endregion
    }
}
